package org.jgs1904.apachepoidemo.utils;


import com.aliyun.oss.OSS;
import com.aliyun.oss.OSSClientBuilder;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.compress.utils.IOUtils;
import org.apache.commons.compress.utils.Lists;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ooxml.POIXMLDocumentPart;
import org.apache.poi.openxml4j.util.ZipSecureFile;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTMarker;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;
import org.springframework.web.multipart.MultipartFile;

import java.io.*;
import java.nio.file.Files;
import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
import java.time.LocalDate;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 使用 Apache POI 读取 Excel 单元格中的图片
 *
 * @author: 默苍璃
 * @date: 2023-10-10 08:47
 */
@Slf4j
@Component
public class ExcelImageReader {

    public static void main(String[] args) throws IOException {
        InputStream inp = new FileInputStream("D:\\temp\\workbookread.xlsx");
        Workbook wb = WorkbookFactory.create(inp);
        XSSFSheet sheet = (XSSFSheet) wb.getSheetAt(0);
        getXlsxPictures(sheet);
    }


    /**
     * 读取上传带图片文件转成Map
     *
     * @param file
     * @return
     * @throws IOException
     */
    public static Map<String, Map<Integer, Map<Integer, String>>> readExcelImageToMap(MultipartFile file) {
        // 读取上传excel
        Workbook wb = readExcel(file);
        if (wb == null) {
            return Collections.emptyMap();
        }
        // 获取总页数
        int pageSize = wb.getNumberOfSheets();

        // excel 里边的sheet 有多个时 限制只取第一个。
        if (pageSize > 1) {
            pageSize = 1;
        }
        // 声明所有页的集合
        Map<String, Map<Integer, Map<Integer, String>>> mapSheet = new HashMap<>();

        for (int i = 0; i < pageSize; i++) {
            // 声明当前页的行和列
            Map<Integer, Map<Integer, String>> map = new HashMap<>();
            // 获取当前页
            Sheet sheet = wb.getSheetAt(i);

            String sheetName = sheet.getSheetName();
            log.info("当前sheet名称:" + sheetName);
            // 获取不为空的总行数
//            int rowSize = sheet.getPhysicalNumberOfRows();
            int rowSize = sheet.getLastRowNum();
            log.info("总行数:" + rowSize);
            // 遍历每一行
            for (int rowNum = 0; rowNum < rowSize; rowNum++) {
                Row row = sheet.getRow(rowNum);
                if (row == null) {
                    continue;
                }
                // 获取不为空的列个数
                //int columnSize = row.getPhysicalNumberOfCells();
                // 获取最后一个不为空的列是第几个
                int columnSize = row.getLastCellNum();
                // 声明当前列
                Map<Integer, String> columnMap = new HashMap<>();
                // 遍历一行中每列值
                for (int cellNum = 0; cellNum < columnSize; cellNum++) {
                    System.out.println(row.getCell(cellNum));
                    Cell cell = row.getCell(cellNum);
                    if (cell != null) {
                        cell.setCellType(CellType.STRING);
                        String value = cell.toString();
                        columnMap.put(cellNum, value);
                    }
//                    String value = (String) getCellValue(row.getCell(cellNum));
                    // 添加当前列的内容 cellNum代表第几列 value是内容

                }
                // 添加当前行的内容 rowNum 代表第几行 value是列的内容 意思是第几行第几列的内容
                map.put(rowNum, columnMap);
            }

            // 声明当前页图片的集合
            Map<String, PictureData> sheetImageMap = null;
            // 获取图片
            try {
                //2003版本的excel，用.xls结尾
                sheetImageMap = getPicturesHSS((HSSFSheet) sheet);
            } catch (Exception ex) {
                log.error(ex.getMessage());
                try {
                    //2007版本的excel，用.xlsx结尾
                    sheetImageMap = getPicturesXSS((XSSFSheet) sheet);
                } catch (Exception e) {
                    log.error(ex.getMessage());
                }
            }
            //解析图片并上传到服务器 并设置该字段的值为字符串类型添加到map中 进行数据库上传
            Object key[] = sheetImageMap.keySet().toArray();
            for (int p = 0; p < sheetImageMap.size(); p++) {
                PictureData pic = sheetImageMap.get(key[p]);
                String picName = key[p].toString();
                String ext = pic.suggestFileExtension();
                byte[] picData = pic.getData();
                try {
                    // 上传图片到服务器并返回访问路径
                    String imageUrl = uploadPicture(picData, ext);
                    if (org.apache.commons.lang3.StringUtils.isNotEmpty(imageUrl)) {
                        //按（行-列）规则解析key 并根据key 设置 某一行的某一列的 图片链接
                        String[] split = picName.split("-");
                        Integer rowIndex = Integer.parseInt(split[0].toString()), columnIndex = Integer.parseInt(split[1].toString());
                        //根据行下标 获取所有的列
                        Map<Integer, String> columns = map.get(rowIndex);
                        //根据列下标 设置图片链接值
                        columns.put(columnIndex, imageUrl);
                    }
                } catch (Exception e) {
                    log.error(e.getMessage());
                }
            }
            // 添加当前页的所有内容
            mapSheet.put(sheetName, map);
        }
        return mapSheet;
    }


    /**
     * 这是.xlsx文件拿到图片位置通用方法
     */
    public static Map<String, List<PictureData>> getXlsxPictures(XSSFSheet sheet) throws IOException {
        // 创建一个用于存储图片的 HashMap
        Map<String, List<PictureData>> map = new HashMap<>();
        // 获取工作表中的所有关系
        List<POIXMLDocumentPart> list = sheet.getRelations();
        // 遍历每个关系
        for (POIXMLDocumentPart part : list) {
            // 判断关系是否为绘图对象
            if (part instanceof XSSFDrawing) {
                // 将关系强制转换为绘图对象
                XSSFDrawing drawing = (XSSFDrawing) part;
                // 获取绘图对象中的所有形状
                List<XSSFShape> shapes = drawing.getShapes();
                // 遍历每个形状
                for (XSSFShape shape : shapes) {
                    // 将形状强制转换为图片对象
                    XSSFPicture picture = (XSSFPicture) shape;
                    // 获取图片的预期大小及位置
                    XSSFClientAnchor anchor = picture.getPreferredSize();
                    // 获取图片的锚点位置
                    CTMarker marker = anchor.getFrom();
                    // 以行号-列号作为唯一标识符
                    String key = marker.getRow() + "-" + marker.getCol();
                    System.out.println(key);
                    // 获取该位置已有的图片列表
                    List<PictureData> pictureDatas = map.get(key);
                    // 如果图片列表为空
                    if (CollectionUtils.isEmpty(pictureDatas)) {
                        // 创建新的图片列表
                        List<PictureData> newList = Lists.newArrayList();
                        // 将图片数据添加到列表中
                        newList.add(picture.getPictureData());
                        // 将新的图片列表加入到 HashMap 中
                        map.put(key, newList);

                        // 如果图片列表不为空
                    } else {
                        // 将图片数据添加到列表中
                        pictureDatas.add(picture.getPictureData());
                    }
                }
            }
        }
        return map; // 返回存储图片的 HashMap
    }

    /**
     * 获取图片和位置 (xls)
     *
     * @param sheet
     * @return
     * @throws IOException
     */
    public static Map<String, PictureData> getPicturesHSS(HSSFSheet sheet) {
        Map<String, PictureData> map = new HashMap<String, PictureData>();
        List<HSSFShape> list = sheet.getDrawingPatriarch().getChildren();
        for (HSSFShape shape : list) {
            if (shape instanceof HSSFPicture) {
                HSSFPicture picture = (HSSFPicture) shape;
                HSSFClientAnchor cAnchor = (HSSFClientAnchor) picture.getAnchor();
                PictureData pdata = picture.getPictureData();
                String key = cAnchor.getRow1() + "-" + cAnchor.getCol1(); // 行号-列号
                map.put(key, pdata);
            }
        }
        return map;
    }

    /**
     * 获取图片和位置 (xlsx)
     *
     * @param sheet
     * @return
     * @throws IOException
     */
    private static Map<String, PictureData> getPicturesXSS(XSSFSheet sheet) {
        Map<String, PictureData> sheetIndexPicMap = new HashMap<String, PictureData>();
        for (POIXMLDocumentPart dr : sheet.getRelations()) {
            if (dr instanceof XSSFDrawing) {
                XSSFDrawing drawing = (XSSFDrawing) dr;
                List<XSSFShape> shapes = drawing.getShapes();
                for (XSSFShape shape : shapes) {
                    XSSFPicture pic = (XSSFPicture) shape;
                    //解决图片空指针报错问题   2021-12-27
                    XSSFClientAnchor anchor = (XSSFClientAnchor) shape.getAnchor();
                    //XSSFClientAnchor anchor = pic.getPreferredSize();
                    CTMarker marker = anchor.getFrom();
                    String key = marker.getRow() + "-" + marker.getCol(); // 行号-列号
                    sheetIndexPicMap.put(key, pic.getPictureData());
                }
            }
        }
        return sheetIndexPicMap;
    }

    /**
     * 这是.xls文件拿到图片位置通用方法
     */
    public static Map<String, List<PictureData>> getXlsPictures(HSSFSheet sheet) throws IOException {
        Map<String, List<PictureData>> map = new HashMap<>();
        List<HSSFShape> hapelList = sheet.getDrawingPatriarch().getChildren();
        for (HSSFShape shape : hapelList) {
            if (shape instanceof HSSFPicture) {
                HSSFPicture picture = (HSSFPicture) shape;
                HSSFClientAnchor cAnchor = (HSSFClientAnchor) picture.getAnchor();
                PictureData pdata = picture.getPictureData();
                // 行号-列号
                String key = cAnchor.getRow1() + "-" + cAnchor.getCol1();
                List<PictureData> pictureDatas = map.get(key);
                if (CollectionUtils.isEmpty(pictureDatas)) {
                    List<PictureData> list = Lists.newArrayList();
                    list.add(pdata);
                    map.put(key, list);
                } else {
                    pictureDatas.add(pdata);
                }
            }
        }
        return map;
    }


    /**
     * 读取excel
     *
     * @param file
     * @return
     */
    public static Workbook readExcel(MultipartFile file) {
        Workbook wb = null;
        ZipSecureFile.setMinInflateRatio(0);
        if (file == null) {
            return null;
        }
        String filename = file.getOriginalFilename();
        InputStream is = null;

        try {
            is = file.getInputStream();
            //2003版本的excel，用.xls结尾
            wb = new HSSFWorkbook(is);//得到工作簿
        } catch (Exception ex) {
            log.error(ex.getMessage());
            try {
                //2007版本的excel，用.xlsx结尾
                is = file.getInputStream();
                wb = new XSSFWorkbook(is);//得到工作簿
            } catch (IOException e) {
                log.error(ex.getMessage());
            }
        }
        log.error("读取成功！");
        return wb;
    }


    /**
     * 将图片上传，返回图片存储路径
     *
     * @param pictureData
     * @param ext
     * @return
     * @throws IOException
     */
    public static String uploadPicture(byte[] pictureData, String ext) throws IOException {
        //SimpleDateFormat ymd = new SimpleDateFormat("yyyyMMdd");
        //SimpleDateFormat ymdHm = new SimpleDateFormat("yyyyMMddHHmm");
        //Date nowDate = new Date();
        //String dateDir = ymd.format(nowDate);// /sysFiles/offlineOrderPicture/ 目录下的日期文件夹名称
        String fileDir = "uploadxlspic/"; // 线下图片上传所在文件夹路径
        InputStream inputStream = new ByteArrayInputStream(pictureData);
        // 获取图片哈希值
        String imageHash = null;
        try {
            imageHash = getMD5Checksum(inputStream);
        } catch (Exception e) {
            e.printStackTrace();
        }
        //String fileName = picIndexName + "_" + imageHash; // 新图片文件名是 excel图片索引+图片哈希
        String newFileName = imageHash + "." + ext; // 文件名（加后缀）

        // 将图片按路径和文件名上传到服务器
        inputStream = new ByteArrayInputStream(pictureData);
        File file = File.createTempFile(fileDir, newFileName);
        OutputStream outputStream = new FileOutputStream(file);

        //保存在本地
//        File file1 = new File("D:\\temp\\" + newFileName);
//        try (OutputStream outputStream1 = Files.newOutputStream(file1.toPath())) {
//            byte[] buffer = new byte[1024];
//            int bytesRead;
//            while ((bytesRead = inputStream.read(buffer)) != -1) {
//                outputStream1.write(buffer, 0, bytesRead);
//            }
//        } catch (IOException e) {
//            e.printStackTrace();
//        }

        //IOUtils.copy() 是 Apache Commons IO 库中的一个方法，用于将输入流的内容复制到输出流中。
        IOUtils.copy(inputStream, outputStream);
        inputStream.close();
        outputStream.close();
        String savePath = upload(file, newFileName, fileDir);
        file.delete();
        return savePath;
    }


    public static String getMD5Checksum(InputStream is) throws NoSuchAlgorithmException, IOException {
        byte[] buffer = new byte[1024];
        MessageDigest complete = MessageDigest.getInstance("MD5");

        int numRead;
        do {
            numRead = is.read(buffer);
            if (numRead > 0) {
                complete.update(buffer, 0, numRead);
            }
        } while (numRead != -1);

        if (is != null) {
            is.close();
        }

        byte[] digest = complete.digest();
        String result = "";

        for (int i = 0; i < digest.length; ++i) {
            result = result + Integer.toString((digest[i] & 255) + 256, 16).substring(1);
        }

        return result;
    }


    private static String accessKeyId;

    @Value("${erp.oss.accessKeyId:}")
    public void setAccessKeyId(String accessKeyId) {
        ExcelImageReader.accessKeyId = accessKeyId;
    }


    private static String accessKeySecret;

    @Value("${erp.oss.accessKeySecret:}")
    public void setAccessKeySecret(String accessKeySecret) {
        ExcelImageReader.accessKeySecret = accessKeySecret;
    }

    private static String endpoint;

    @Value("${erp.oss.endpoint:}")
    public void setEndpoint(String endpoint) {
        ExcelImageReader.endpoint = endpoint;
    }

    private static String bucket;

    @Value("${erp.oss.bucket:}")
    public void setBucket(String bucket) {
        ExcelImageReader.bucket = bucket;
    }

    //OSS存储路径

    public static String upload(File file, String fileName, String rootPath) {
        LocalDate now = LocalDate.now();
        // 存储路径
        String path = rootPath + now.getYear() + "-" + now.getMonthValue() + "-" + now.getDayOfMonth() + "/" + fileName.replace("+", "_");
        // oss
        OSS client = new OSSClientBuilder().build(endpoint, accessKeyId, accessKeySecret);
        // 存储文件
        client.putObject(bucket, path, file);
        String url = "https://" + bucket + "." + endpoint + "/" + path;
        log.info(url);
        return url;
    }


}